package qingke.app;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import qingke.pojo.Client;
import qingke.pojo.Order;
import qingke.pojo.Product;
import qingke.pojo.User;
import qingke.util.Console;

public class OrderDao {
	public OrderDao() {

	}

	public User signup(String username, String password, String email, String phone) {
		String sql = "INSERT INTO `order`.`user` ( `username`, `password`,`email`,`phone`) VALUES (?,?,?,?)";

		Connection conn = null;
		PreparedStatement statement = null;

		try {
			conn = getConnection();
			statement = conn.prepareStatement(sql);

			statement.setString(1, username);
			statement.setString(2, password);
			statement.setString(3, email);
			statement.setString(4, phone);

			int affectRows = statement.executeUpdate();

			if (affectRows > 0) {
				return login(username, password);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			cleanup(conn, statement, null);
		}

		return null;
	}

	public Client complete(User user, String username, String name, String phone) {
		String sql = "INSERT INTO `order`.`client` ( `name`, `phone`, `client_type_id`, `client_status_id`) VALUES (?,?,?,?)";

		Connection conn = null;
		PreparedStatement statement = null;

		try {
			conn = getConnection();
			statement = conn.prepareStatement(sql);

			statement.setString(1, name);
			statement.setString(2, phone);
			statement.setInt(3, 1);
			statement.setInt(4, 1);

			int affectRows = statement.executeUpdate();

			if (affectRows > 0) {
				return getClient(user, username, name, phone);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			cleanup(conn, statement, null);
		}

		return null;
	}

	public Client getClient(User user, String username, String name, String phone) {
		String sql = "SELECT id, name, phone,client_type_id,client_status_id FROM client WHERE name = ? AND phone = ?";

		Connection conn = null;
		PreparedStatement statement = null;
		ResultSet rs = null;

		try {
			conn = getConnection();
			statement = conn.prepareStatement(sql);

			statement.setString(1, name);
			statement.setString(2, phone);

			rs = statement.executeQuery();

			Client client = null;
			while (rs.next()) {
				int id = rs.getInt("id");
				String name1 = rs.getString("name");
				String phone1 = rs.getString("phone");
				int client_type_id = rs.getInt("client_type_id");
				int client_status_id = rs.getInt("client_status_id");
				client = new Client(id, name1, phone1);
				client.setClientStatusID(client_status_id);
				client.setClientTypeID(client_type_id);
				setClientID(username, id);
				setAccount(id);
				user.setClientID(id);
			}
			return client;
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		} finally {
			cleanup(conn, statement, rs);
		}
	}

	public void setClientID(String username, int clientID) {
		String sql = "UPDATE `order`.`user` SET `client_id`=? WHERE `username`=?";

		Connection conn = null;
		PreparedStatement statement = null;

		try {
			conn = getConnection();
			statement = conn.prepareStatement(sql);

			statement.setInt(1, clientID);
			statement.setString(2, username);
			statement.execute();

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			cleanup(conn, statement, null);
		}

	}

	public User login(String username, String password) {
		String sql = "SELECT id, username, email,phone,client_id FROM user WHERE username = ? AND password = ?";

		Connection conn = null;
		PreparedStatement statement = null;
		ResultSet rs = null;

		try {
			conn = getConnection();
			statement = conn.prepareStatement(sql);

			statement.setString(1, username);
			statement.setString(2, password);

			rs = statement.executeQuery();

			User user = null;
			while (rs.next()) {
				int id = rs.getInt("id");
				String name = rs.getString("username");
				String email = rs.getString("email");
				String phone = rs.getString("phone");
				int client_id = rs.getInt("client_id");
				user = new User(id, name);
				user.setEmail(email);
				user.setPhone(phone);
				user.setClientID(client_id);

			}
			return user;
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		} finally {
			cleanup(conn, statement, rs);
		}
	}

	public void createOrder(String time, int clientID) {
		String sql = "INSERT INTO `order`.`order1` ( `create_datetime`,`payment_status`) VALUES (?,?)";

		Connection conn = null;
		PreparedStatement statement = null;

		try {
			conn = getConnection();
			statement = conn.prepareStatement(sql);

			statement.setString(1, time);
			statement.setString(2, "F");
			// statement.setInt(2, clientID);

			statement.execute();
			setOrder(time, clientID);

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			cleanup(conn, statement, null);
		}
	}

	public void setOrder(String time, int clientID) {
		String sql = "UPDATE `order`.`order1` SET `client_id`=? WHERE `create_datetime`=?";

		Connection conn = null;
		PreparedStatement statement = null;

		try {
			conn = getConnection();
			statement = conn.prepareStatement(sql);

			statement.setInt(1, clientID);
			statement.setString(2, time);
			statement.execute();

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			cleanup(conn, statement, null);
		}

	}

	public int getOrderID(String time, int clientID) {
		String sql = "SELECT id FROM order1 WHERE create_datetime = ? AND client_id = ?";

		Connection conn = null;
		PreparedStatement statement = null;
		ResultSet rs = null;

		try {
			conn = getConnection();
			statement = conn.prepareStatement(sql);

			statement.setString(1, time);
			statement.setInt(2, clientID);

			rs = statement.executeQuery();

			while (rs.next()) {
				int id = rs.getInt("id");
				return id;
			}

		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		} finally {
			cleanup(conn, statement, rs);
		}
		return 0;
	}

	public String getPaymentStatus(int orderID) {
		String sql = "SELECT payment_status FROM order1 WHERE id = ?";

		Connection conn = null;
		PreparedStatement statement = null;
		ResultSet rs = null;
		String paymentStatus = null;
		try {
			conn = getConnection();
			statement = conn.prepareStatement(sql);

			statement.setInt(1, orderID);

			rs = statement.executeQuery();

			while (rs.next()) {
				paymentStatus = rs.getString("payment_status");

			}

		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		} finally {
			cleanup(conn, statement, rs);
		}
		return paymentStatus;
	}

	public void setPaymentStatus(int orderID) {
		String sql = "UPDATE `order`.`order1` SET `payment_status`=? WHERE `id`=?";

		Connection conn = null;
		PreparedStatement statement = null;

		try {
			conn = getConnection();
			statement = conn.prepareStatement(sql);

			statement.setString(1, "T");
			statement.setInt(2, orderID);
			statement.execute();

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			cleanup(conn, statement, null);
		}

	}

	public void setOrderItem(int orderID, int amount, int productID) {
		String sql = "INSERT INTO `order`.`order_item` ( `amount`, `product_id`,`order_id`) VALUES (?,?,?)";

		Connection conn = null;
		PreparedStatement statement = null;

		try {
			conn = getConnection();
			statement = conn.prepareStatement(sql);

			statement.setInt(1, amount);
			statement.setInt(2, productID);
			statement.setInt(3, orderID);

			statement.execute();

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			cleanup(conn, statement, null);
		}

	}

	public ArrayList<Product> getProduct() {
		String sql = "SELECT id,name, price,catalog_id FROM product ";
		ArrayList<Product> products = new ArrayList<Product>();

		Connection conn = null;
		PreparedStatement statement = null;
		ResultSet rs = null;

		try {
			conn = getConnection();
			statement = conn.prepareStatement(sql);

			rs = statement.executeQuery();

			Product product = null;
			while (rs.next()) {
				int id = rs.getInt("id");
				String name = rs.getString("name");
				double price = rs.getDouble("price");
				int catalog_id = rs.getInt("catalog_id");
				String catalog = getCatalog(catalog_id);
				product = new Product(id, name, price, catalog);
				products.add(product);
			}
			return products;
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		} finally {
			cleanup(conn, statement, rs);
		}
	}

	public ArrayList<Order> getOrder(User user) {
		String sql = "SELECT id,create_datetime , client_id FROM order1 where client_id = ?";
		ArrayList<Order> orders = new ArrayList<Order>();

		Connection conn = null;
		PreparedStatement statement = null;
		ResultSet rs = null;

		try {
			conn = getConnection();
			statement = conn.prepareStatement(sql);
			statement.setInt(1, getClientFromUser(user, user.getName()));
			rs = statement.executeQuery();
			Order order = new Order();
			while (rs.next()) {
				int id = rs.getInt("id");
				String createDatetime = rs.getString("create_datetime");
				int clientID = rs.getInt("client_id");
				order = new Order(id, createDatetime, clientID);
				ArrayList<Product> products = getProduct(id);
				order.setProducts(products);
				orders.add(order);
			}
			return orders;
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		} finally {
			cleanup(conn, statement, rs);
		}
	}

	public ArrayList<Product> getProduct(int orderID) {
		String sql = "SELECT product_id,amount FROM order_item WHERE order_id = ?";
		ArrayList<Product> products = new ArrayList<Product>();
		Connection conn = null;
		PreparedStatement statement = null;
		ResultSet rs = null;

		try {
			conn = getConnection();
			statement = conn.prepareStatement(sql);

			statement.setInt(1, orderID);
			rs = statement.executeQuery();

			while (rs.next()) {
				int productID = rs.getInt("product_id");
				int amount = rs.getInt("amount");
				Product product = getProductFromItem(productID);
				product.setAmount(amount);
				products.add(product);
			}

		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		} finally {
			cleanup(conn, statement, rs);
		}
		return products;
	}

	public Product getProductFromItem(int productID) {
		String sql = "SELECT id,name,price,catalog_id FROM product WHERE id = ?";

		Connection conn = null;
		PreparedStatement statement = null;
		ResultSet rs = null;
		Product product = null;

		try {
			conn = getConnection();
			statement = conn.prepareStatement(sql);

			statement.setInt(1, productID);
			rs = statement.executeQuery();
			while (rs.next()) {
				int id = rs.getInt("id");
				String name = rs.getString("name");
				double price = rs.getInt("price");
				int catalogID = rs.getInt("catalog_id");
				String catalog = getCatalog(catalogID);
				product = new Product(id, name, price, catalog);
			}

		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		} finally {
			cleanup(conn, statement, rs);
		}
		return product;
	}

	public int getClientFromUser(User user, String username) {
		String sql = "SELECT client_id FROM user WHERE username = ?";

		Connection conn = null;
		PreparedStatement statement = null;
		ResultSet rs = null;

		try {
			conn = getConnection();
			statement = conn.prepareStatement(sql);

			statement.setString(1, username);

			rs = statement.executeQuery();

			int id = 0;
			if (rs.next()) {
				id = rs.getInt("client_id");
			}
			return id;
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		} finally {
			cleanup(conn, statement, rs);
		}
	}

	public String getCatalog(int catalog_id) {
		String sql = "SELECT name FROM catalog where id = " + catalog_id;

		Connection conn = null;
		PreparedStatement statement = null;
		ResultSet rs = null;

		try {
			conn = getConnection();
			statement = conn.prepareStatement(sql);

			rs = statement.executeQuery();

			String name = null;
			while (rs.next()) {
				name = rs.getString("name");

			}
			return name;
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		} finally {
			cleanup(conn, statement, rs);
		}
	}

	public void setAccount(int clientID) {
		String sql = "INSERT INTO `order`.`account` ( `client_id`) VALUES (?)";

		Connection conn = null;
		PreparedStatement statement = null;

		try {
			conn = getConnection();
			statement = conn.prepareStatement(sql);

			statement.setInt(1, clientID);

			statement.execute();

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			cleanup(conn, statement, null);
		}

	}

	public void changeAccount(User user, double money, int clientID) {
		String sql = "UPDATE `order`.`account` SET `balance`=? WHERE `client_id`=?";

		Connection conn = null;
		PreparedStatement statement = null;
		int balance = getAccount(user);
		double newBalance = balance + money;
		try {
			conn = getConnection();
			statement = conn.prepareStatement(sql);

			statement.setDouble(1, newBalance);
			statement.setInt(2, clientID);
			statement.execute();

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			cleanup(conn, statement, null);
		}

	}

	public int getAccount(User user) {
		String sql = "SELECT balance FROM account WHERE client_id = ? ";

		Connection conn = null;
		PreparedStatement statement = null;
		ResultSet rs = null;

		try {
			conn = getConnection();
			statement = conn.prepareStatement(sql);

			statement.setInt(1, getClientFromUser(user, user.getName()));

			rs = statement.executeQuery();

			while (rs.next()) {
				int balance = rs.getInt("balance");
				return balance;
			}

		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		} finally {
			cleanup(conn, statement, rs);
		}
		return 0;
	}

	public double getOrderMoney(int orderID) {
		String sql = "SELECT product_id,amount FROM order_item WHERE order_id = ? ";

		Connection conn = null;
		PreparedStatement statement = null;
		ResultSet rs = null;
		double om = 0;
		try {
			conn = getConnection();
			statement = conn.prepareStatement(sql);

			statement.setInt(1, orderID);

			rs = statement.executeQuery();

			while (rs.next()) {
				int productID = rs.getInt("product_id");
				double pm = getProductMoney(productID);
				int amount = rs.getInt("amount");
				om = om + pm * amount;
			}

		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		} finally {
			cleanup(conn, statement, rs);
		}
		return om;
	}

	public double getProductMoney(int productID) {
		String sql = "SELECT price FROM product WHERE id = ?";

		Connection conn = null;
		PreparedStatement statement = null;
		ResultSet rs = null;
		double price = 0;

		try {
			conn = getConnection();
			statement = conn.prepareStatement(sql);

			statement.setInt(1, productID);
			rs = statement.executeQuery();
			while (rs.next()) {
				price = rs.getInt("price");
			}

		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		} finally {
			cleanup(conn, statement, rs);
		}
		return price;
	}

	public boolean isUsernameExists(String username) {
		String sql = "select 'Y' from user where username = ?";

		Connection conn = null;
		PreparedStatement statement = null;
		ResultSet rs = null;

		try {
			conn = getConnection();
			statement = conn.prepareStatement(sql);

			statement.setString(1, username);

			rs = statement.executeQuery();

			if (rs.next()) {
				return true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		} finally {
			cleanup(conn, statement, rs);
		}

		return false;
	}

	private Connection getConnection() {
		// com.mysql.jdbc.Driver.class.getName();
		Connection conn = null;
		String driver = "com.mysql.jdbc.Driver";
		String url = "jdbc:mysql://localhost:3306/order?useSSL=false";
		String username = "root";
		String password = "zx19950410";

		try {
			Class.forName(driver);
		} catch (ClassNotFoundException e) {
			throw new RuntimeException(e);
		}
		try {
			conn = DriverManager.getConnection(url, username, password);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return conn;
	}

	private void cleanup(Connection conn, Statement statement, ResultSet rs) {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				throw new RuntimeException(e);
			}
		}
		if (statement != null) {
			try {
				statement.close();
			} catch (SQLException e) {
				throw new RuntimeException(e);
			}
		}
		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				throw new RuntimeException(e);
			}
		}
	}

}
